package reference;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

import lib.Global;




import reference.Property;
import test.testLog4j;

import weibo4j.model.Status;
import weibo4j.model.User;
import weibo4j.model.UserWapper;


public class Database {
	static Logger logger = Logger.getLogger(testLog4j.class.getName());

	static public String getUidFromName(String name, Connection con) throws SQLException{
		PreparedStatement ps = null;
		ResultSet rs = null;
 		try {
			String SQL = "SELECT * FROM basicInfo WHERE sourceName=?";
			ps= con.prepareStatement(SQL);
			ps.setString(1, name);
			rs = ps.executeQuery();
			if(rs.next()){
				return rs.getString("uid");
			}
			else{
				return null;	
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(name+ " exist error");
			return null;
		} finally{
			if(rs != null) rs.close();
			if(ps != null)ps.close();
		}
	}
	
	static public void insertUserList(UserWapper users, Connection con){
		for(User user : users.getUsers()){
			try {
				insertUser(user,con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("users list insert error");
			}
		}
	}
	
	
	static public boolean exitRelation(String uid, String follow, int type,Connection con) throws SQLException{
		PreparedStatement ps = null;
		ResultSet rs = null;
 		try {
			String SQL = "SELECT * FROM userRelation WHERE uid=? and type=?";
			ps= con.prepareStatement(SQL);
			ps.setString(1, uid);
			ps.setBoolean(2, type==1);
			rs = ps.executeQuery();
			while(rs.next()){
				if(follow.equals(rs.getString("fid"))){
					System.out.println(uid+ " relaiton" +follow+" exist.");
					return true;
				}
									
			}			
			return false;	
			
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(uid+ " relaiton" +follow+" exist error.");
			return true;
		} finally{
			rs.close();
			ps.close();
		}
	}
	
	static public void insertRelation(String uid,String follow, int flag, Connection con) throws SQLException{
		String SQL = "INSERT INTO userRelation (uid,fid,type) VALUES (?,?,?)";
		PreparedStatement pst = null;
			try{
				pst = con.prepareStatement(SQL);
				pst.setString(1, uid);
				pst.setString(2, follow);
				pst.setInt(3, flag);
				pst.executeUpdate();
				System.out.println(uid+ " relaiton" +follow+" insert.");
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println(uid+ " relaiton" +follow+" insert.");
			} finally {
				if(pst!=null)
				pst.close();
			}

	}
	
	static public void insertStatus(Status aStatus, Connection con) throws SQLException, IOException{
		String SQL ="INSERT INTO status " +
				"(uid,fid,flag,id,mid,createdAt,text,sourceUrl,sourceRelationship,sourceName,retweetedStatus,repostsCount,commentsCount,favorited) " +
				"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement pst = null;
		try{
			pst = con.prepareStatement(SQL);
			if(aStatus.getUser()==null){
				pst.setString(1, "error");
				pst.setLong(6, 0);
				pst.setString(8, "error");
				pst.setString(9, "error");
				pst.setString(10, "error");
			}
			else{
				pst.setString(1, aStatus.getUser().getId());
				pst.setLong(6, aStatus.getCreatedAt().getTime());
				pst.setString(8, aStatus.getSource().getUrl());
				pst.setString(9, aStatus.getSource().getRelationship());
				pst.setString(10, aStatus.getSource().getName());
			}
			pst.setString(4, aStatus.getId());
			pst.setString(5, aStatus.getMid());
			pst.setString(7, aStatus.getText());
			if(aStatus.getRetweetedStatus()!=null){
				pst.setString(11, aStatus.getRetweetedStatus().getMid());
			}
				
			else {
				pst.setString(11, null);
			}
			pst.setLong(12, aStatus.getRepostsCount());
			pst.setLong(13, aStatus.getCommentsCount());
			pst.setBoolean(14, aStatus.isFavorited());
			if(aStatus instanceof MyStatus){
				MyStatus a = (MyStatus)aStatus;
				pst.setString(2, a.getFid());
				pst.setInt(3, a.getFlag());
			}else{
				pst.setString(2, "super");
				pst.setInt(3, 0);
			}
			pst.executeUpdate();
			
			
		}catch (SQLException e) {
			e.printStackTrace();
			RecordError.WriteError(aStatus.getMid(), "insert single status error");
		} finally {
			if(pst!=null)
				pst.close();
		}
	}
	

	
	static public List<String> getAllUserId(Connection con) throws SQLException{
		List<String> Users =  new ArrayList<String>();
		String SQL = "SELECT uid FROM basicInfo";
		Statement st = null;
		ResultSet rs = null;
		try{
			st = con.createStatement();
			rs = st.executeQuery(SQL);
			while(rs.next()){
				Users.add(rs.getString("uid"));
			}
			return Users;
			
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("Get all users' id failed!");
			return null;
			
		}finally{
			if(rs != null) rs.close();
			if(st != null) st.close();
		}
		
	}
	public static List<String> getFollowById(String uid, Connection con) throws IOException, SQLException{
		String SQL = "SELECT * FROM userRelation WHERE uid =? and type=?";
		List<String> followerList = new ArrayList<String>();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try{
			pst = con.prepareStatement(SQL);
			pst.setString(1, uid);
			pst.setBoolean(2, true);
			rs = pst.executeQuery();
			while(rs.next()){
				followerList.add(rs.getString("fid"));
			}
			return followerList;
		}catch(Exception e){
			e.printStackTrace();
			RecordError.WriteError(uid, "Get follower list from database");
			return null;
		}finally{
			if(rs != null)rs.close();
			if(pst != null)pst.close();
		}
	}
	
	public static List<String> getFriendById(String uid, Connection con) throws IOException, SQLException{
		String SQL = "SELECT * FROM userRelation WHERE uid =? and type=?";
		List<String> followerList = new ArrayList<String>();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try{
			pst = con.prepareStatement(SQL);
			pst.setString(1, uid);
			pst.setBoolean(2, false);
			rs = pst.executeQuery();
			while(rs.next()){
				followerList.add(rs.getString("fid"));
			}
			return followerList;
		}catch(Exception e){
			e.printStackTrace();
			RecordError.WriteError(uid, "Get follower list from database");
			return null;
		}finally{
			if(rs != null)rs.close();
			if(pst != null)pst.close();
		}
	}
	public static List<String> getFollowByIds(List<String> userlist, Connection con) throws IOException, SQLException{
		List<String> allfollow = new ArrayList<String>();
		for(String uid : userlist){
			List<String> receive = getFollowById(uid, con);
			int size = allfollow.size();
			for(String follow : receive){
				int flag = 0;
				for(int i = 0; i < size; i++){
					if(follow.equals(allfollow.get(i))){ 
						flag = 1;
						break;
					}
				}
				if(flag == 0) allfollow.add(follow);
			}
		}
		return allfollow;
	}
	
	public static List<String> getRelationByIds(List<String> userlist, Connection con) throws IOException, SQLException{
		List<String> allRelation = new ArrayList<String>();
		for(String uid : userlist){
			List<String> receive = getFollowById(uid, con);
			List<String> receive2 = getFriendById(uid, con);
			allRelation.addAll(receive);
			allRelation.addAll(receive2);
		}
		return allRelation;
	}
	
	public static int getStatusNum(String uid, Connection con) throws SQLException{
		String SQL = "SELECT * FROM Status WHERE uid = ?";
		PreparedStatement pst = null;
		ResultSet rs = null;
		int num = 0;
		try{
			pst = con.prepareStatement(SQL);
			pst.setString(1, uid);
			rs = pst.executeQuery();
			while(rs.next()){
				num++;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null)rs.close();
			if(pst != null)pst.close();
		}
		return num;
	}
	public static List<Status> getTextById(String uid, Connection con, int num){
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Status> statusList = new ArrayList<Status>();
		String SQL = "SELECT text FROM status WHERE uid = ? and createdAt < ?";
		try {
			ps = con.prepareStatement(SQL);
			ps.setString(1, uid);
			Calendar ca = Calendar.getInstance();
			ca.clear();
			ca.set(Calendar.YEAR, 10);
			ca.set(Calendar.DAY_OF_MONTH, 15);
			ps.setLong(2, ca.getTimeInMillis());
			rs = ps.executeQuery();
			while(rs.next()){
				Status aStatus = new Status();
				
				rs.getString("text");

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
